Hi Scripting guy,
I used one of the tutorials I found at this website on reading excel file sheet. Its working fine on my need only problem is I get an error on the objFile.Write arrSheet part once I start writing from array number 8 above. Please help thanks in advance.
Option ExplicitDim arrSheet, intCount, outFile, objFSO, objFile, MyEP, MyRS
't500 set assuming 16 sites max on channel Map explore t500 change to ubound value
arrSheet = ReadExcel( MyEP, MyRS, "B6", "L500", False ) 'set to true if you do not need headers
'setup to assign output of readexcel as loadfile name
x = split(loadfile,".")
filename = x(0)
WScript.Echo "check van = " & filename
'to write results to a file
outFile = "C:\Temp\simulation_function\"& file &".txt" 'feb 6
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(outFile,True)
For intCount = 0 To UBound( arrSheet, 2 )
'to write to cmd use below wscript.Echo
'WScript.Echo arrSheet( 0, intCount ) & vbTab & arrSheet( 1, intCount )
objFile.Write arrSheet( 0, intCount ) & vbTab & arrSheet( 1, intCount ) & vbTab & arrSheet( 2, intCount ) & vbTab & arrSheet(3, intCount ) & vbTab & arrSheet( 4,
intCount ) & vbTab & arrSheet( 5, intCount ) & vbTab & arrSheet( 6, intCount ) & vbTab & arrSheet( 7, intCount ) & vbTab & arrSheet( 8, intCount ) & vbCrLf
Next
objFile.Close
WScript.Echo "==============="
'=================================================
'===========Read excel function ==================
'=================================================
Function ReadExcel( myXlsFile, mySheet, my1stCell, myLastCell, blnHeader )
' Function : ReadExcel
' Version : 3.00
' This function reads data from an Excel sheet without using MS-Office
'
' Arguments:
' myXlsFile [string] The path and file name of the Test Program to be read by the script
' mySheet [string] The name of the Channnel Map of the worksheet that needs to be read (could be many in an excel file)
' my1stCell [string] The index of the first cell to be read from channel map
' myLastCell [string] The index of the last cell to be read from channel map
' blnHeader [boolean] True if the first row in the sheet is a header
'
' Returns:
' The values read from the Excel sheet are returned in a two-dimensional
' array; the first dimension holds the columns, the second dimension holds
' the rows read from the Excel sheet.
Dim arrData( ), i, j
Dim objExcel, objRS
Dim strHeader, strRange
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
' Define header parameter string for Excel object
If blnHeader Then
strHeader = "HDR=YES;"
Else
strHeader = "HDR=NO;"
End If
' Open the object for the Excel file
Set objExcel = CreateObject( "ADODB.Connection" )
' With IMEX=1 numbers won't be ignored; tip by Thomas Willig.
' Connection string updated by Marcel Ninkemper to open Excel 2007 (.xslx) files.
objExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& myXlsFile & ";Extended Properties=""Excel 12.0;IMEX=1;" _
& strHeader & """"
' Open a recordset object for the sheet and range
Set objRS = CreateObject( "ADODB.Recordset" )
strRange = mySheet & "$" & my1stCell & ":" & myLastCell
objRS.Open "Select * from [" & strRange & "]", objExcel, adOpenStatic
' Read the data from the Excel sheet
i = 0
Do Until objRS.EOF
' Stop reading when an empty row is encountered in the Excel sheet
If IsNull( objRS.Fields(0).Value ) Or Trim( objRS.Fields(0).Value ) = "" Then Exit Do
' Add a new row to the output array
ReDim Preserve arrData( objRS.Fields.Count - 1, i )
' Copy the Excel sheet's row values to the array "row"
' IsNull test credits: Adriaan Westra
For j = 0 To objRS.Fields.Count - 1
If IsNull( objRS.Fields(j).Value ) Then
arrData( j, i ) = " test "
Else
arrData( j, i ) = Trim( objRS.Fields(j).Value )
End If
Next
' Move to the next row
objRS.MoveNext
' Increment the array "row" number
i = i + 1
Loop
' Return the results
' Close the file and release the objects
objRS.Close
objExcel.Close
Set objRS = Nothing
Set objExcel = Nothing
ReadExcel = arrData
End Function
- Edited by Mckulet 23 hours 1 minutes ago edited code
Other recent topics
